﻿using System;
using System.Data;
using System.Data.SqlClient;
using QLTH_DTO;
namespace QLTH_DATASSECC
{
   public class clsHocSinhDA: ConnectionData.clsKetNoi
    {
       String _sql;

       public DataTable DanhSachHocSinh()
       {
           _sql = "select hs.MAHS, (hs.HOLOTHS+'  '+hs.TENHS) as HOTEN, (CASE WHEN hs.PHAI = 0 then 'Nam' else N'Nữ' end) as PHAI, hs.NGAYSINH, hs.DIACHI, hs.NOISINH, tg.TENTONGIAO as TONGIAO, dt.TENDANTOC as DANTOC, pl.TENPL as PHANLOAI, hs.NGAYNHAPHOC from HOCSINH hs  inner join DANTOC dt on hs.MADT = dt.MADT inner join TONGIAO tg on hs.MATG = tg.MATG inner join PHANLOAIHS pl on hs.MAPL = pl.MAPL";
           return Load_DataTable(_sql);
       }

       public DataTable TimKiemHStheoDK(String _dk)
       {
           String dk = "WHERE freetext(HS.*, N'"+_dk+"')";
           _sql = "select MAHS, (HOLOTHS+' '+TENHS) as HOTEN, (CASE WHEN HS.PHAI = 0 THEN 'Nam' ELSE N'Nữ' end) AS PHAI, HS.NGAYSINH, HS.DIACHI, HS.NOISINH, TG.TENTONGIAO AS TONGIAO, DT.TENDANTOC AS DANTOC, PL.TENPL AS PHANLOAI, HS.NGAYNHAPHOC from HOCSINH HS inner join DANTOC DT ON HS.MADT= DT.MADT INNER JOIN TONGIAO TG ON HS.MATG=TG.MATG INNER JOIN PHANLOAIHS PL ON HS.MAPL=PL.MAPL" + dk; 
           return Load_DataTable(_sql);

       }

       public void Open_Excel(String _filename)
       {
           ketnoi_excel(_filename);
        }

       


       public DataTable DanhSachHocSinhExcel()
       {
           _sql = "select * from [SHEET1$]";
           return TableExcel(_sql);
       }

       public int ThemHS(clsHocSinhDTO hs)
       {

           int namepara = 14;
           String[] name = new String[namepara];
           Object[] values = new Object[namepara];
           name[0] = "@Mahs"; values[0] = hs.Mahs;
           name[1] = "@Mapl"; values[1] = hs.Phanloai.Mapl;
           name[2] = "@Madt"; values[2] = hs.Dantoc.Madt;
           name[3] = "@Matg"; values[3] =hs.Tongiao.Matg;
           name[4] = "@Holot"; values[4] =hs.Holoths ;
           name[5] = "@Ten"; values[5] = hs.Tenhs;
           name[6] = "@Phai"; values[6] =hs.Phai ;
           name[7] = "@Ngaysinh"; values[7] = hs.Ngaysinh;
           name[8] = "@Diachi"; values[8] = hs.Diachi;
           name[9] = "@NoiSinh"; values[9] = hs.Noisinh;
           name[10] = "@Ngaynhaphoc"; values[10] = hs.Ngaynhaphoc;
           name[11] = "@Anh"; values[11] = hs.Anh;
           name[12] = "@Madut"; values[12] = hs.Uutien.Maut;
           name[13] = "@Doanvien"; values[13] = hs.Doanvien;
           _sql = "INSERT INTO HOCSINH VALUES(@Mahs,@Mapl,@Madut,@Madt,@Matg,@Holot,@Ten,@Phai,@Ngaysinh,@Diachi,@Noisinh,@Ngaynhaphoc,@Anh,@Doanvien)";
           return Nonquery(_sql,name,values,namepara);
       
       }

       public int CapNhatHS(clsHocSinhDTO hs)
       {
           int namepara = 14;
           String[] name = new String[namepara];
           Object[] values = new Object[namepara];
           name[0] = "@Mahs"; values[0] = hs.Mahs;
           name[1] = "@Mapl"; values[1] = hs.Phanloai.Mapl;
           name[2] = "@Madt"; values[2] = hs.Dantoc.Madt;
           name[3] = "@Matg"; values[3] = hs.Tongiao.Matg;
           name[4] = "@Holot"; values[4] = hs.Holoths;
           name[5] = "@Ten"; values[5] = hs.Tenhs;
           name[6] = "@Phai"; values[6] = hs.Phai;
           name[7] = "@Ngaysinh"; values[7] = hs.Ngaysinh;
           name[8] = "@Diachi"; values[8] = hs.Diachi;
           name[9] = "@NoiSinh"; values[9] = hs.Noisinh;
           name[10] = "@Ngaynhaphoc"; values[10] = hs.Ngaynhaphoc;
           name[11] = "@Anh"; values[11] = hs.Anh;
           name[12] = "@Madut"; values[12] = hs.Uutien.Maut;
           name[13] = "@Doanvien"; values[13] = hs.Doanvien;
           _sql = "update HOCSINH set MAPL=@Mapl,MADUT = @Madut, MADT=@Madt , MATG=@Matg, HOLOTHS=@Holot,TENHS=@Ten,PHAI=@Phai, NGAYSINH =@Ngaysinh, DIACHI=@Diachi, NOISINH=@Noisinh, NGAYNHAPHOC=@Ngaynhaphoc, ANH=@Anh,DOANVIEN = @Doanvien where MAHS=@Mahs";
           return Nonquery(_sql,name,values,namepara);
       }

       public int XoaHS(clsHocSinhDTO hs)
       {
           int namepara = 1;
           String[] name = new String[namepara];
           Object[] values = new Object[namepara];
           name[0] = "@Mahs"; values[0] = hs.Mahs;
           _sql = "DELETE HOCSINH WHERE MAHS=@Mahs";
           return Nonquery(_sql,name,values,namepara);
       }

       public DataTable Lay_HS_TheoMa(String _mahs)
       {
           _sql = "select MAHS, (HOLOTHS+' '+TENHS) as HOTEN, (CASE WHEN HS.PHAI = 0 THEN 'Nam' ELSE N'Nữ' end) AS PHAI, HS.NGAYSINH, HS.DIACHI, HS.NOISINH, TG.TENTONGIAO AS TONGIAO, DT.TENDANTOC AS DANTOC, PL.TENPL AS PHANLOAI, HS.NGAYNHAPHOC from HOCSINH HS inner join DANTOC DT ON HS.MADT= DT.MADT INNER JOIN TONGIAO TG ON HS.MATG=TG.MATG INNER JOIN PHANLOAIHS PL ON HS.MAPL=PL.MAPL WHERE MAHS='"+_mahs+"'";
           return Load_DataTable(_sql);
       }


       public string macuoi()
       {
           _sql = "SELECT TOP 1 MAHS FROM HOCSINH ORDER BY MAHS DESC";
           return laymacuoi(_sql);
       }
    }
}
